package com.xiguaji.tiktok.v1.ins.media.repository;

import com.xiguaji.tiktok.v1.ins.blogger.dto.InsBloggerCfsData;
import com.xiguaji.tiktok.v1.ins.media.dto.*;
import com.xiguaji.tiktok.v1.ins.media.model.InsMedia;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;
import java.util.Optional;

/**
 * 帖子dao
 * @author Huang xin
 */
public interface InsMediaRepository extends CrudRepository<InsMedia,Long>,InsMediaExtRepository {

    /**
     * 根据uid 获取帖子
     * @param uid
     * @param limitTime
     * @return
     */
    @Query("SELECT id,owner_uid,caption,shortcode,display_url,is_video,video_url,video_view_count,comment_count,like_count," +
            " taken_time,er * 100 er " +
            " FROM `ins_media` where owner_uid = :uid and taken_time > :limitTime ")
    List<InsMedia> findByOwnerUidAndTakenTime(long uid,long limitTime);

    /**
     * 根据uid 获取广告帖子得分数据
     * @param uid
     * @return
     */
    @Query("select ad_count,ad_ec,brand_media_er * 100 ad_media_er,brand_media_rate * 100 ad_media_rate, " +
            "ad_video_count,ad_video_ec,ad_video_er * 100 ad_video_er,ad_video_rate * 100 ad_video_rate, " +
            "ad_image_count,ad_image_ec,ad_image_er * 100 ad_image_er,ad_image_rate * 100 ad_image_rate, " +
            "ad_video_evc from ins_st_blogger_real_following where uid = :uid")
    Optional<InsAdMediaGoalResponse> findAdMediaGoalByOwnerUid(long uid);

    /**
     * 根据uid 查询广告帖子标记品牌分类信息
     * @param uid
     * @param limitTime
     * @return
     */
    @Query("SELECT overall_category_name category_name, count(1) brand_count FROM " +
            "( SELECT DISTINCT t2.tagger_uid " +
            " FROM (SELECT shortcode FROM ins_media WHERE owner_uid = :uid  AND is_ad = 1 and taken_time > :limitTime ) t1 " +
            " LEFT JOIN ins_rel_media_tagger_user t2 ON t1.shortcode = t2.shortcode) t3 " +
            " INNER JOIN ins_blogger t4 ON t3.tagger_uid = t4.uid " +
            " WHERE t4.is_business = 1 GROUP BY overall_category_name order by brand_count desc ")
    List<InsAdMediaBrandCategory> findAdMediaBrandCategoryByUid(long uid,long limitTime);

    /**
     * 根据uid 查询广告帖子标记品牌帖子分类信息
     * @param uid
     * @param limitTime
     * @return
     */
    @Query("select t5.overall_category_name category_name,count(1) brand_count from " +
            "(SELECT t3.shortcode, t4.overall_category_name FROM (SELECT t2.tagger_uid,t2.shortcode " +
            " FROM ( SELECT shortcode FROM ins_media WHERE owner_uid = :uid AND is_ad = 1 and taken_time > :limitTime  ) t1 " +
            " LEFT JOIN ins_rel_media_tagger_user t2 ON t1.shortcode = t2.shortcode) t3 " +
            " INNER JOIN ins_blogger t4 ON t3.tagger_uid = t4.uid " +
            " WHERE t4.is_business = 1 GROUP BY t3.shortcode,t4.overall_category_name)t5 " +
            " group by t5.overall_category_name order by brand_count desc ")
    List<InsAdMediaBrandCategory> findAdTagBrandMediaCategoryByUid(long uid,long limitTime);

    /**
     * 根据uid 查询广告帖子标记品牌帖子分类信息
     * @param uid
     * @param limitTime
     * @return
     */
    @Query("SELECT count(distinct shortcode) total " +
            " FROM ( SELECT t2.tagger_uid,t2.shortcode " +
            " FROM (SELECT shortcode FROM ins_media WHERE owner_uid = :uid AND is_ad = 1 and taken_time > :limitTime ) t1 " +
            " LEFT JOIN ins_rel_media_tagger_user t2 ON t1.shortcode = t2.shortcode) t3" +
            " INNER JOIN ins_blogger t4 ON t3.tagger_uid = t4.uid " +
            " WHERE t4.is_business = 1 ")
    int findAdTagBrandMediaCategoryTotalByUid(long uid,long limitTime);

    /**
     * 根据uid 获取播主帖子信息
     * @param uid
     * @param limitTime
     * @return
     */
    @Query("select count(1) total,round(avg(er) * 100,2) avg_er,round(avg(like_count),0) avg_like,round(avg(comment_count),0) avg_comment," +
            " round(avg(video_view_count),0) avg_video_view_count from ins_media " +
            " where owner_uid = :uid and taken_time > :limitTime ")
    Optional<InsBloggerMediaInfo> findBloggerMediaInfoByOwnerUid(long uid, long limitTime);

    /**
     * 根据uid 获取 账户近30天帖子平均参与率、同分类同量级平均值、超越人数百分比
     * @param uid
     * @return
     */
    @Query("select t2.media_er exceed,t1.avg_er rate,t3.media_er * 100 same_cfs_rate from " +
            "(select uid,follower_size,overall_category_name,avg_er * 10000 exceed,avg_er * 100 avg_er from ins_blogger_main where uid = :uid) t1 " +
            "left join ins_st_blogger_cfs t2 " +
            "on t1.follower_size = t2.follower_size and t1.overall_category_name = t2.category and t2.rate >= t1.exceed " +
            "and t2.rate < t1.exceed + 5 " +
            "left join ins_st_blogger_cfs_avg t3 " +
            "on t1.follower_size = t3.follower_size and t1.overall_category_name = t3.category")
    Optional<InsBloggerCfsData> findSaneCfsMediaAvgErByUid(long uid);

    /**
     * 根据uid 获取 账户近30天广告帖子平均参与率、同分类同量级平均值、超越人数百分比
     * @param uid
     * @return
     */
    @Query("select t2.ad_media_er exceed,t1.brand_media_er rate,t3.ad_media_er * 100 same_cfs_rate from " +
            "(select uid,follower_size,overall_category_name,brand_media_er * 10000 exceed,brand_media_er * 100 brand_media_er " +
            " from ins_blogger_main where uid = :uid) t1 " +
            "left join ins_st_blogger_cfs t2 " +
            "on t1.follower_size = t2.follower_size and t1.overall_category_name = t2.category and t2.rate >= t1.exceed " +
            "and t2.rate < t1.exceed + 5 " +
            "left join ins_st_blogger_cfs_avg t3 " +
            "on t1.follower_size = t3.follower_size and t1.overall_category_name = t3.category")
    Optional<InsBloggerCfsData> findSaneCfsAdMediaAvgErByUid(long uid);

    @Query("select b.uid, b.username, b.full_name, b.follower_count, b.overall_category_name, b.pic_url, b.is_verified, avg(comment_count) avg_reviews,avg(like_count) avg_likes,avg(video_view_count) avg_views,avg(er) avg_er from ins_blogger_main b left join ins_media m on b.uid = m.owner_uid where b.uid = :uid and m.taken_time > :timeRang")
    InsAdBloggerInfo findInsAdBloggerInfoByUid(long uid,long timeRang);


    @Query("select ad_view_rate view_rate,ad_like_rate like_rate,ad_review_rate review_rate,ad_media_er er from ins_dic_follower_size fs left join ins_st_blogger_cfs_avg cfs on fs.id = cfs.follower_size " +
            "where fs.size_start <= :followerCount and fs.size_end >= :followerCount and cfs.category = :category")
    InsMediaInfoRate findInsMediaInfoRateByLevel(long followerCount,String category);

    @Query("select caption_tag from ins_media m left join ins_rel_media_caption_tag t on m.shortcode = t.shortcode where m.id = :id")
    List<String> findInsMediaTags(long id);



    /**
     * 根据uid 以及限制时间 获取 账户发文时间统计数据
     * @param uid
     * @param limitTime
     * @return
     */
    @Query("SELECT avg(er) * 100 avg_er, count(1) total, " +
            " FROM_UNIXTIME(unix_timestamp(convert_tz(FROM_UNIXTIME(taken_time),@@session.time_zone,'UTC')),'%w')post_week, " +
            " FROM_UNIXTIME(unix_timestamp(convert_tz(FROM_UNIXTIME(taken_time),@@session.time_zone,'UTC')),'%H')post_hour " +
            " FROM ins_media WHERE owner_uid = :uid AND taken_time > :limitTime " +
            " group by post_week,post_hour ")
    List<InsMediaTakenTime> findInsMediaTakenTimeInfoByUid(long uid,long limitTime);

    @Query("select * from ins_media where id = :id")
    InsMedia findById(long id);

}
